package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.dubbo.config.annotation.Service;
import com.ndp.fb.enums.ec.EcTemplateMaterielRunStatus;
import com.ndp.fb.rdb.api.EcTemplateMaterielRdbService;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.EcTemplateMateriel;
import com.ndp.fb.rdb.model.EcTemplateMaterielExt;
import com.ndp.fb.util.SQLUtil;
import com.ndp.fb.rdb.model.*;
import com.ndp.fb.util.SQLUtil;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by infi.he on 2016/4/18.
 */
@Service
public class EcTemplateMaterielRdbServiceImpl extends DaoTemplate implements EcTemplateMaterielRdbService {

    @Override
    public EcTemplateMateriel findEcTemplateMaterielById(Long materialId) {
        String sql = "SELECT * from ec_template_materiel where id = ?";
        return findOne(sql, new Object[]{materialId}, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
    }

    @Override
    public EcTemplateMaterielFeedExt findEcTemplateMaterielFeedExtById(Long materialId) {
        String sql = "SELECT\n" +
                "	 ec.*,feed.product_feed_name\n" +
                "FROM\n" +
                "	ec_template_materiel ec,\n" +
                "	product_we_feed feed\n" +
                "WHERE\n" +
                "	ec.feed_id = feed.id\n" +
                "AND ec.id = ?";
        return findOne(sql, new Object[]{materialId}, DefaultRowMapper.autoRowMapper(EcTemplateMaterielFeedExt.class));
    }

    @Override
    public Long save(EcTemplateMateriel ecTemplateMateriel) {
        return saveResultId(ecTemplateMateriel);
    }

    @Override
    public List<EcTemplateMateriel> findByFeedId(Long feedId) {
        String sql = "SELECT * from ec_template_materiel et WHERE  et.feed_id = ? and  et.is_delete = 0 and et.created_status = 2";
        return getJdbcTemplate().query(sql, new Object[]{feedId}, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
    }

    @Override
    public List<EcTemplateMateriel> findAll() {
        return findAll(DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
    }

    @Override
    public List<EcTemplateMateriel> findAllCreated() {
        String sql = "SELECT * from ec_template_materiel et WHERE  et.is_delete = 0 and et.created_status = 2";
        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
    }

    @Override
    public List<EcTemplateMateriel> findAllByStatus(int status) {
        String sql = "SELECT * from ec_template_materiel et WHERE  et.is_delete = 0 and et.created_status = ?";
        return getJdbcTemplate().query(sql, new Object[]{status}, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
    }

    @Override
    public EcTemplateMaterielExt findExtById(Long id) {
        String sql = "SELECT a.ec_template_materiel_id as id,\n" +
                " count(1) as 'cnt',\n" +
                " sum(case when a.adgroup_status in('PENDING_REVIEW','DISAPPROVED','ACTIVE') then '1' ELSE 0 end) as 'running',\n" +
                " sum(case when a.adgroup_status in('PAUSED','CAMPAIGN_PAUSED','CAMPAIGN_GROUP_PAUSED') then '1' ELSE 0 end) as 'paused',\n" +
                " sum(case when a.adgroup_status in('DELETED','ARCHIVED') then '1' ELSE 0 end) as 'deleted'\n" +
                "from ad_group a where a.ec_template_materiel_id = ?";
        return findOne(sql, new Object[]{id}, DefaultRowMapper.autoRowMapper(EcTemplateMaterielExt.class));
    }

    @Override
    public Pager<EcTemplateMateriel> findPagerByName(Integer feedId, User user, String name, List<String> creativeStatus, List<Map<String, List<String>>> categoryList, Integer firstResult, Integer sizeNo, String order) {
        List<List<String>> idsList = getFilterIds(categoryList);
        //只用fiter查询， 不包含运行状态
        if (idsList.size() > 0 && (creativeStatus == null || creativeStatus.size() == 0)) {
            StringBuffer sql = new StringBuffer();
            StringBuffer ttable = new StringBuffer();
            List paramValue = new ArrayList<>();
            sql.append("SELECT * from ec_template_materiel et,\n");
            sql.append("(");
            //内部sql t1
            ttable.append("  SELECT * from ( \n");
            for (int i = 0; i < idsList.size(); i++) {
                List<String> strings = idsList.get(i);
                if (i > 0) {
                    ttable.append("    UNION ALL \n");
                }
                ttable.append("SELECT em.ec_template_materiel_id from ec_materiel_categorys em").append("\n")
                        .append("where em.category_id in(").append(SQLUtil.appendPlaceholder(strings.size())).append(")\n");
                paramValue.addAll(strings);
            }
            ttable.append(") t1 \n");
            ttable.append("group BY t1.ec_template_materiel_id HAVING count(1) >= " + idsList.size() + "\n");
            sql.append(ttable.toString());
            //内部sql t1 完成
            sql.append(") t \n");
            sql.append("where et.id = t.ec_template_materiel_id\n");
            sql.append("and et.org_id = ? \n");
            sql.append("and et.is_delete = 0\n");
            paramValue.add(user.getOrgId());
            if (feedId != null) {
                sql.append("and et.feed_id = ?\n");
                paramValue.add(feedId);
            }
            if (StringUtils.isNotEmpty(name)) {
                sql.append("and et.`name` like ? \n");
                paramValue.add("%" + name + "%");
            }

            addOrder(order, sql);
            return findPager(sql.toString(), paramValue.toArray(), firstResult, sizeNo, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
        }

        //不包含fiter查询， 只包含运行状态
        else if (idsList.size() == 0 && (creativeStatus != null && creativeStatus.size() > 0)) {
            StringBuffer sql = new StringBuffer();
            List paramValue = new ArrayList<>();
            sql.append("SELECT * from ec_template_materiel et\n");
            sql.append("  LEFT JOIN ( \n");
            sql.append("     SELECT a.ec_template_materiel_id ,\n");
            sql.append("      count(1) as 'cnt',\n");
            sql.append("      sum(case when a.adgroup_status in('PENDING_REVIEW','DISAPPROVED','ACTIVE') then '1' ELSE 0 end) as 'running',\n");
            sql.append("      sum(case when a.adgroup_status in('PAUSED','CAMPAIGN_PAUSED','CAMPAIGN_GROUP_PAUSED') then '1' ELSE 0 end) as 'paused',\n");
            sql.append("      sum(case when a.adgroup_status in('DELETED','ARCHIVED') then '1' ELSE 0 end) as 'deleted'\n");
            sql.append("   from ad_group a\n");
            sql.append("   group by ec_template_materiel_id\n");
            sql.append(") t on (et.id = t.ec_template_materiel_id)\n");
            sql.append("where et.org_id = ? \n");
            sql.append("and et.is_delete = 0\n");

            paramValue.add(user.getOrgId());
            if (feedId != null) {
                sql.append("and et.feed_id = ?\n");
                paramValue.add(feedId);
            }
            if (StringUtils.isNotEmpty(name)) {
                sql.append("and et.`name` like ? \n");
                paramValue.add("%" + name + "%");
            }

            String s = buildAdgrupStatusSql(creativeStatus);
            if (s.length() > 2) {
                sql.append("and ").append(s).append("\n");
            }

            addOrder(order, sql);
            return findPager(sql.toString(), paramValue.toArray(), firstResult, sizeNo, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
        }

        //都包含
        else if (idsList.size() > 0 && (creativeStatus != null && creativeStatus.size() > 0)) {
            StringBuffer sql = new StringBuffer();
            StringBuffer ttable = new StringBuffer();
            List paramValue = new ArrayList<>();
            sql.append("SELECT * from ec_template_materiel et,\n");
            sql.append("(");
            //内部sql t1
            ttable.append("  SELECT * from ( \n");

            for (int i = 0; i < idsList.size(); i++) {
                List<String> strings = idsList.get(i);
                if (i > 0) {
                    ttable.append("    UNION ALL \n");
                }
                ttable.append("SELECT em.ec_template_materiel_id from ec_materiel_categorys em").append("\n")
                        .append("where em.category_id in(").append(SQLUtil.appendPlaceholder(strings.size())).append(")\n");
                paramValue.addAll(strings);
            }
            // 运行状态关联
            ttable.append("    UNION ALL \n");
            ttable.append("SELECT et1.id as ec_template_materiel_id from ec_template_materiel et1\n");
            ttable.append("  LEFT JOIN (\n");
            ttable.append("     SELECT a.ec_template_materiel_id ,\n");
            ttable.append("      count(1) as 'cnt',\n");
            ttable.append("      sum(case when a.adgroup_status in('PENDING_REVIEW','DISAPPROVED','ACTIVE') then '1' ELSE 0 end) as 'running',\n");
            ttable.append("      sum(case when a.adgroup_status in('PAUSED','CAMPAIGN_PAUSED','CAMPAIGN_GROUP_PAUSED') then '1' ELSE 0 end) as 'paused',\n");
            ttable.append("      sum(case when a.adgroup_status in('DELETED','ARCHIVED') then '1' ELSE 0 end) as 'deleted' \n");
            ttable.append("   from ad_group a\n");
            ttable.append("   group by a.ec_template_materiel_id\n");
            ttable.append(") t on (et1.id = t.ec_template_materiel_id)\n");
            ttable.append("where et1.is_delete = 0\n");
            ttable.append("and ").append(buildAdgrupStatusSql(creativeStatus)).append("\n");
            // 运行状态关联

            ttable.append(") t1 \n");
            ttable.append("group BY t1.ec_template_materiel_id HAVING count(1) >= " + (idsList.size() + 1) + "\n");
            sql.append(ttable.toString());

            //内部sql t1 完成
            sql.append(") temp \n");
            sql.append("where et.id = temp.ec_template_materiel_id\n");
            sql.append("and et.org_id = ? \n");
            sql.append("and et.is_delete = 0\n");
            paramValue.add(user.getOrgId());
            if (feedId != null) {
                sql.append("and et.feed_id = ?\n");
                paramValue.add(feedId);
            }
            if (StringUtils.isNotEmpty(name)) {
                sql.append("and et.`name` like ? \n");
                paramValue.add("%" + name + "%");
            }

            addOrder(order, sql);
            return findPager(sql.toString(), paramValue.toArray(), firstResult, sizeNo, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
        }
        // 都不包含
        else {
            StringBuffer sql = new StringBuffer();
            List paramValue = new ArrayList<>();

            sql.append("SELECT * from ec_template_materiel et where et.is_delete = 0 and et.org_id = ? ");
            paramValue.add(user.getOrgId());
            if (feedId != null) {
                sql.append("and et.feed_id = ?\n");
                paramValue.add(feedId);
            }
            if (StringUtils.isNotEmpty(name)) {
                sql.append("and et.`name` like ? \n");
                paramValue.add("%" + name + "%");
            }
            addOrder(order, sql);
            return findPager(sql.toString(), paramValue.toArray(), firstResult, sizeNo, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
        }
    }

    private String buildAdgrupStatusSql(List<String> creativeStatus) {
        StringBuffer sql = new StringBuffer("(");

        for (int i = 0; i < creativeStatus.size(); i++) {
            String s = creativeStatus.get(i);
            if (i > 0) {
                sql.append(" or ");
            }
            //未使用
            if ("NotUsed".equalsIgnoreCase(s)) {
                sql.append(" t.cnt is null ");
            } else if ("Running".equalsIgnoreCase(s)) {
                sql.append(" t.running > 0 ");
            } else if ("Paused".equalsIgnoreCase(s)) {
                sql.append(" t.paused > 0 ");
            } else if ("Deleted".equalsIgnoreCase(s)) {
                sql.append(" t.deleted > 0 ");
            }
        }
        sql.append(")");
        return sql.toString();
    }


    @Override
    public Pager<EcTemplateMateriel> findPagerByTag(Integer feedId, User user, Long tagId, List<String> creativeStatus, List<Map<String, List<String>>> categoryList, Integer firstResult, Integer sizeNo, String order) {
        List<List<String>> idsList = getFilterIds(categoryList);

        // 只有filter
        if (idsList.size() > 0 && (creativeStatus == null || creativeStatus.size() == 0)) {
            StringBuffer sql = new StringBuffer();
            StringBuffer ttable = new StringBuffer();
            List paramValue = new ArrayList<>();
            sql.append("SELECT et.* from ec_template_materiel et,ec_materiel_tags tags,\n");
            sql.append("(");

            //内部sql
            ttable.append("  SELECT * from (\n");
            for (int i = 0; i < idsList.size(); i++) {
                List<String> strings = idsList.get(i);
                if (i > 0) {
                    ttable.append("    UNION ALL \n");
                }
                ttable.append("SELECT em.ec_template_materiel_id from ec_materiel_categorys em").append("\n")
                        .append("where em.category_id in(").append(SQLUtil.appendPlaceholder(strings.size())).append(")\n");
                paramValue.addAll(strings);
            }
            ttable.append(") t1 \n");
            ttable.append("group BY t1.ec_template_materiel_id HAVING count(1) >= " + (idsList.size()) + "\n");
            sql.append(ttable.toString());

            sql.append(") t \n");
            sql.append("where et.id = t.ec_template_materiel_id\n");
            sql.append("and tags.ec_materiel_tag_id = ? \n");
            sql.append("and tags.ec_template_materiel_id = et.id\n");
            sql.append("and et.org_id = ? \n");
            sql.append("and et.is_delete = 0 \n");
            paramValue.add(tagId);
            paramValue.add(user.getOrgId());
            if (feedId != null) {
                sql.append("and et.feed_id = ?\n");
                paramValue.add(feedId);
            }
            addOrder(order, sql);
            return findPager(sql.toString(), paramValue.toArray(), firstResult, sizeNo, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
        }
        // 只有运行状态
        else if (idsList.size() == 0 && creativeStatus != null && creativeStatus.size() > 0) {
            StringBuffer sql = new StringBuffer();
            List paramValue = new ArrayList<>();
            sql.append("SELECT et.* from ec_template_materiel et\n");
            sql.append("  LEFT JOIN ( \n");
            sql.append("     SELECT a.ec_template_materiel_id ,\n");
            sql.append("      count(1) as 'cnt',\n");
            sql.append("      sum(case when a.adgroup_status in('PENDING_REVIEW','DISAPPROVED','ACTIVE') then '1' ELSE 0 end) as 'running',\n");
            sql.append("      sum(case when a.adgroup_status in('PAUSED','CAMPAIGN_PAUSED','CAMPAIGN_GROUP_PAUSED') then '1' ELSE 0 end) as 'paused',\n");
            sql.append("      sum(case when a.adgroup_status in('DELETED','ARCHIVED') then '1' ELSE 0 end) as 'deleted'\n");
            sql.append("   from ad_group a\n");
            sql.append("   group by ec_template_materiel_id\n");
            sql.append(") t on (et.id = t.ec_template_materiel_id)\n");
            sql.append("INNER JOIN ec_materiel_tags tags on (tags.ec_template_materiel_id = et.id and tags.ec_materiel_tag_id = ?)\n");
            sql.append("where et.org_id = ? \n");
            sql.append("and et.is_delete = 0\n");
            paramValue.add(tagId);
            paramValue.add(user.getOrgId());
            if (feedId != null) {
                sql.append("and et.feed_id = ?\n");
                paramValue.add(feedId);
            }
            String s = buildAdgrupStatusSql(creativeStatus);
            if (s.length() > 2) {
                sql.append("and ").append(s).append("\n");
            }

            addOrder(order, sql);
            return findPager(sql.toString(), paramValue.toArray(), firstResult, sizeNo, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
        }
        // 包含状态。包含
        else if (idsList.size() > 0 && creativeStatus != null && creativeStatus.size() > 0) {
            StringBuffer sql = new StringBuffer();
            StringBuffer ttable = new StringBuffer();
            List paramValue = new ArrayList<>();
            sql.append("SELECT et.* from ec_template_materiel et,\n");
            sql.append("(");
            //内部sql t1
            ttable.append("  SELECT * from ( \n");

            for (int i = 0; i < idsList.size(); i++) {
                List<String> strings = idsList.get(i);
                if (i > 0) {
                    ttable.append("    UNION ALL \n");
                }
                ttable.append("SELECT em.ec_template_materiel_id from ec_materiel_categorys em").append("\n")
                        .append("where em.category_id in(").append(SQLUtil.appendPlaceholder(strings.size())).append(")\n");
                paramValue.addAll(strings);
            }
            // 运行状态关联
            ttable.append("    UNION ALL \n");
            ttable.append("SELECT et1.id as ec_template_materiel_id from ec_template_materiel et1\n");
            ttable.append("  LEFT JOIN (\n");
            ttable.append("     SELECT a.ec_template_materiel_id ,\n");
            ttable.append("      count(1) as 'cnt',\n");
            ttable.append("      sum(case when a.adgroup_status in('PENDING_REVIEW','DISAPPROVED','ACTIVE') then '1' ELSE 0 end) as 'running',\n");
            ttable.append("      sum(case when a.adgroup_status in('PAUSED','CAMPAIGN_PAUSED','CAMPAIGN_GROUP_PAUSED') then '1' ELSE 0 end) as 'paused',\n");
            ttable.append("      sum(case when a.adgroup_status in('DELETED','ARCHIVED') then '1' ELSE 0 end) as 'deleted' \n");
            ttable.append("   from ad_group a\n");
            ttable.append("   group by a.ec_template_materiel_id\n");
            ttable.append(") t on (et1.id = t.ec_template_materiel_id)\n");
            ttable.append("where et1.is_delete = 0\n");
            ttable.append("and ").append(buildAdgrupStatusSql(creativeStatus)).append("\n");
            // 运行状态关联

            //关联tag
            ttable.append("    UNION ALL \n");
            ttable.append("SELECT tags.ec_template_materiel_id from ec_materiel_tags tags WHERE tags.ec_materiel_tag_id = ?\n");
            paramValue.add(tagId);
            //关联tag

            ttable.append(") t1 \n");
            ttable.append("group BY t1.ec_template_materiel_id HAVING count(1) >= " + (idsList.size() + 2) + "\n");
            sql.append(ttable.toString());

            //内部sql t1 完成
            sql.append(") temp \n");
            sql.append("where et.id = temp.ec_template_materiel_id\n");
            sql.append("and et.org_id = ? \n");
            sql.append("and et.is_delete = 0\n");
            paramValue.add(user.getOrgId());
            if (feedId != null) {
                sql.append("and et.feed_id = ?\n");
                paramValue.add(feedId);
            }
            addOrder(order, sql);
            return findPager(sql.toString(), paramValue.toArray(), firstResult, sizeNo, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
        } else {
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT\n");
            sql.append("  et.*\n");
            sql.append("FROM\n");
            sql.append("    ec_materiel_tags tags,\n");
            sql.append("    ec_template_materiel et\n");
            sql.append("WHERE\n");
            sql.append("    et.id = tags.ec_template_materiel_id\n");
            sql.append("AND et.is_delete = 0\n");
            sql.append("AND et.org_id = ?\n");
            sql.append("AND tags.ec_materiel_tag_id = ?\n");
            if (feedId != null) {
                sql.append("and et.feed_id = ?\n");
            }
            addOrder(order, sql);
            return findPager(sql.toString(), new Object[]{user.getOrgId(), tagId, feedId}, firstResult, sizeNo, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
        }
    }

    private void addOrder(String order, StringBuffer sql) {
        if ("1".equals(order)) {
            sql.append("ORDER BY et.create_time desc");
        } else {
            sql.append("ORDER BY et.create_time ");
        }
    }

    /**
     * 将前台传递的参转化成id序列
     *
     * @param categoryList
     * @return
     */
    private List<List<String>> getFilterIds(List<Map<String, List<String>>> categoryList) {
        List<List<String>> idsList = new ArrayList<>();
        if (categoryList != null) {
            for (Map<String, List<String>> stringListMap : categoryList) {
                if (stringListMap != null) {
                    for (Map.Entry<String, List<String>> stringListEntry : stringListMap.entrySet()) {
                        List<String> ids = new ArrayList<>();
                        List<String> strings = stringListEntry.getValue();
                        for (String string : strings) {
                            if (StringUtils.isNotEmpty(string)) {
                                ids.add(string);
                            }
                        }
                        if (ids.size() > 0) {
                            idsList.add(ids);
                        }
                    }
                }
            }

        }
        return idsList;
    }

    @Override
    public void updateProductCountAndCreatedImgNum(int size, Long createdImgNo, Long materialId) {
        String sql = "update ec_template_materiel e set e.product_count = ? , e.created_num = ? ,e.update_time = ? where e.id = ?";
        getJdbcTemplate().update(sql, new Object[]{size, createdImgNo, new Date(), materialId});
    }

    @Override
    public void updateCreatedImgNum(Long createdImgNo, Long materialId) {
        String sql = "update ec_template_materiel e set  e.created_num = ? ,update_time = ? where e.id = ?";
        getJdbcTemplate().update(sql, new Object[]{createdImgNo, new Date(), materialId});
    }

    @Override
    public void updateCreatedStatus(Integer s, Date createDate, Long materialId) {
        if (createDate == null) {
            String sql = "update ec_template_materiel e set e.created_status = ? where e.id = ?";
            getJdbcTemplate().update(sql, new Object[]{s, materialId});
        } else {
            String sql = "update ec_template_materiel e set e.created_status = ? , e.create_time = ? where e.id = ?";
            getJdbcTemplate().update(sql, new Object[]{s, createDate, materialId});
        }
    }

    @Override
    public void updateCreatedStatus(Integer s, Long materialId) {
        String sql = "update ec_template_materiel e set e.created_status = ? , e.update_time = ? where e.id = ?";
        getJdbcTemplate().update(sql, new Object[]{s, new Date(), materialId});
    }

    @Override
    public void delete(Long orgId, Long id) {
        String sql = "DELETE from ec_template_materiel where id = ? and org_id = ?";
        getJdbcTemplate().update(sql, new Object[]{id, orgId});
    }

    @Override
    public EcTemplate findEcTemplateByMaterielId(Long materialId) {
        String sql = "SELECT * from ec_template et, ec_template_materiel etm \n" +
                "where et.id = etm.template_id\n" +
                "and etm.id = ?";
        return findOne(sql, new Object[]{materialId}, DefaultRowMapper.autoRowMapper(EcTemplate.class));
    }

    @Override
    public void updateMaterielCodeAndThumbnailAndMaterielContext(String materialCode, String thumbnail, String html, Long id) {
        String sql = "update  ec_template_materiel set materiel_code=? ,thumbnail=? , materiel_context=?  where id= ?";
        getJdbcTemplate().update(sql, new Object[]{materialCode, thumbnail, html, id});

    }

    @Override
    public EcTemplateMateriel findByNameAndOrgId(String name, Long orgId) {
        String sql = "SELECT * from ec_template_materiel WHERE name =? and org_id=?";
        return findOne(sql, new Object[]{name, orgId}, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));

    }

    @Override
    public void updateName(String name, Long ecTemplateMaterielId) {
        String sql = "update  ec_template_materiel set name=?   where id= ?";
        getJdbcTemplate().update(sql, new Object[]{name, ecTemplateMaterielId});
    }

    @Override
    public List<EcTemplateMateriel> findByWeFeedIdAndNames(String feedId, List<String> creativeTemplate) {
        String sql = "select * from ec_template_materiel where feed_id = ? and name in (" + SQLUtil.appendPlaceholder(creativeTemplate.size()) + ")";
        creativeTemplate.add(0, feedId);
        return getJdbcTemplate().query(sql, creativeTemplate.toArray(), DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
    }

    @Override
    public List<EcTemplateMateriel> findByIds(Object[] objects) {
        String sql = "select * from ec_template_materiel where id in (" + SQLUtil.appendPlaceholder(objects.length) + ")";
        return getJdbcTemplate().query(sql, objects, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
    }


    @Override
    public void updateNameAndTemplateId(String name, Long templateId, Long ecTemplateMaterielId) {
        String sql = "update  ec_template_materiel set template_id = ?, name=?  where id= ?";
        getJdbcTemplate().update(sql, new Object[]{templateId, name, ecTemplateMaterielId});
    }

    @Override
    public void updateTemplateCode(String templateCode, Long ecTemplateMaterielId) {
        String sql = "update  ec_template_materiel set template_code = ? where id= ?";
        getJdbcTemplate().update(sql, new Object[]{templateCode, ecTemplateMaterielId});
    }

    @Override
    public void updateCreatedStatusAndNewFeedUrl(int type, Date date, String descScPath, Long materialId) {
        String sql = "update ec_template_materiel e set e.created_status = ? , e.update_time = ? , e.new_feed_url = ? where e.id = ?";
        getJdbcTemplate().update(sql, new Object[]{type, date, descScPath, materialId});
    }

    @Override
    public List<EcTemplateMateriel> findByFeedIdAndCreativeType(Long feedId, int type) {
        String sql = "SELECT ec.category_id,et.* "
                + "FROM ec_template_materiel et INNER JOIN ec_materiel_categorys ec ON et.id = ec.ec_template_materiel_id "
                + "WHERE  et.feed_id = ? and  et.is_delete = 0 and et.created_status = 2 AND (ec.category_id=? or ec.parent_category_id = ?)";
        return getJdbcTemplate().query(sql, new Object[]{feedId, type, type}, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
    }

    @Override
    public List<Long> findCategoryIdByMaterielId(Long id) {
        String sql = "SELECT ec.category_id "
                + "FROM ec_materiel_categorys ec "
                + "WHERE ec.is_delete = 0 AND ec.ec_template_materiel_id=?";
        return getJdbcTemplate().queryForList(sql, new Object[]{id}, Long.class);
    }

    @Override
    public List<EcTemplateMateriel> findEcTemplateMaterielCanUseCreateAd(Long feedId) {
        String sql = "SELECT * from ec_template_materiel et\n" +
                "where et.is_delete = 0 \n" +
                "and (et.created_status = 2 or  (et.new_feed_url is not null && et.created_status = 1))\n" +
                "and et.feed_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{feedId}, DefaultRowMapper.autoRowMapper(EcTemplateMateriel.class));
    }

    @Override
    public List<EcTemplateMaterielAndElementCategory> findCategoryAndElement(Long ecTemplateMaterielId) {
        String sql = "SELECT 'elementType' AS parent_type," +
                "       IF(eme.`major` = 1, 'major', 'minor') AS 'type'," +
                "       eec.`context` AS 'name'" +
                " FROM ec_materiel_elements AS eme," +
                "     ec_element_category AS eec" +
                " WHERE eme.`template_materiel_id` = ?" +
                "  AND eme.`is_delete` = 0" +
                "  AND eec.`code` = eme.`parent_code`" +
                " UNION ALL" +
                " SELECT DISTINCT emc2.`category_type` AS `parent_type`," +
                "                emc.`category_type` AS `type`," +
                "                emc.`category_us_name` AS `name`" +
                " FROM ec_materiel_categorys AS emcs," +
                "     ec_materiel_category AS emc" +
                " LEFT JOIN ec_materiel_category AS emc2 ON emc.`pid` = emc2.`id`" +
                " WHERE emcs.`ec_template_materiel_id` = ?" +
                "  AND emcs.`is_delete` = 0" +
                "  AND emcs.`category_id` = emc.`id`";
        return getJdbcTemplate().query(sql, new Object[]{ecTemplateMaterielId, ecTemplateMaterielId}, DefaultRowMapper.autoRowMapper(EcTemplateMaterielAndElementCategory.class));
    }
}
